if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_NCAAPairings_NCAATeams]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[NCAAPairings] DROP CONSTRAINT FK_NCAAPairings_NCAATeams
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAADeleteUserPrediction]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAADeleteUserPrediction]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAGetResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAGetResults]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAGetUserPicks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAGetUserPicks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAInsertResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAInsertResults]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAInsertUserPrediction]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAInsertUserPrediction]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAGetRoundPoints]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAGetRoundPoints]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAADeleteRoundPoints]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAADeleteRoundPoints]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAListPoolStandings]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAListPoolStandings]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAGetModuleList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAGetModuleList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAGameRound]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NCAAGameRound]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAPairings]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NCAAPairings]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAPredictions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NCAAPredictions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAResults]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NCAAResults]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAARounds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NCAARounds]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAATeams]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NCAATeams]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAPreceedingGames]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NCAAPreceedingGames]
GO

if exists(select * from sysobjects where name = 'vw_NCAAPoolStandings')
   drop view vw_NCAAPoolStandings
GO

if exists(select * from sysobjects where name = 'fn_NCAAGetPoolStandingsByRank')
   drop function  fn_NCAAGetPoolStandingsByRank
GO

CREATE TABLE [dbo].[NCAAGameRound] (
	[GameId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RoundId] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NCAAPairings] (
	[GameId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TeamId] [int] NULL,
	[TeamSeed] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NCAAPredictions] (
	[PredictionId] [int] IDENTITY (1, 1) NOT NULL ,
	[PortalId] int NULL,
	[ModuleId] [int] NULL ,
	[UserId] [int] NULL ,
	[GameId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TeamId] [int] NULL ,
	[GameScore] [int] NULL ,
	[TeamAScore] [int] NULL ,
	[TeamBScore] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NCAAResults] (
	[PortalId] int,
	[ModuleId] int,
	[GameId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TeamId] [int] NULL ,
	[GameScore] [int] NULL ,
	[TeamAScore] [int] NULL ,
	[TeamBScore] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NCAARounds] (
	[PortalId] int,
	[ModuleId] [int] NULL ,
	[RoundId] [int] NULL ,
	[RoundName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RoundPoints] [int] NULL,
  	[RoundGames] int NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NCAATeams] (
	[TeamId] [int] IDENTITY (1, 1) NOT NULL ,
	[TeamName] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NCAAPreceedingGames] (
	[GameId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TeamAId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TeamBId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO

create view vw_NCAAPoolStandings
as
select
  u.UserId as 'UserId',
  b.PortalId as 'PredictionsPortalId',
  b.ModuleId as 'PredictionsModuleId',
  d.PortalId as 'RoundsPortalId',
  d.ModuleId as 'RoundsModuleId',
  max(u.username) as 'UserName',
  max(u.FirstName + ' '+ u.LastName) as 'UserFirstLastName',
  count(b.gameid) as 'TotalPicks',
sum((CASE 
    WHEN a.TeamId = B.TeamId THEN 1 
    ELSE 0 
    END) * d.RoundPoints) AS 'TotalPoints',
sum(CASE 
    WHEN a.TeamId = B.TeamId THEN 1 
    ELSE 0 
    END) AS 'CorrectPicks',
sum(
  CASE 
    WHEN a.TeamId = B.TeamId THEN 0 
    ELSE 1
    END) AS 'IncorrectPicks'  
from 
  NCAAResults a,
  NCAAPredictions b,
  NCAAGameRound c,
  NCAARounds d,
  Users u 
where a.GameId = b.GameId
  and a.ModuleId = b.ModuleId
  and a.GameId = c.GameId
  and c.RoundId = d.RoundId
  and  b.UserId = u.Userid
group by 
  u.UserId,  
  b.PortalId,
  b.ModuleId,
  d.PortalId,
  d.ModuleId  

GO

create function  fn_NCAAGetPoolStandingsByRank(@PortalId int, @ModuleId int)
returns table
as
return 
(
select 
  UserId,
  PredictionsPortalId,
  PredictionsModuleId,
  RoundsPortalId,
  RoundsModuleId,
  UserName,
  UserFirstLastName,
  TotalPicks,
  TotalPoints,
  CorrectPicks,
  IncorrectPicks, 
  (select count (s2.TotalPoints)
from vw_NCAAPoolStandings s2
where
s1.TotalPoints < s2.TotalPoints) + 1 as  'rank'
from vw_NCAAPoolStandings s1
where PredictionsPortalId = @PortalId
  and PredictionsModuleId = @ModuleId
  and RoundsPortalId = @PortalId
  and RoundsModuleId = @ModuleId

)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[NCAADeleteUserPrediction] 
  @ModuleId as int,
  @UserId as int
AS

/*
  This procedure deletes all the predictions for a particular user, the module does not matter
  for this version
*/

delete from dbo.NCAAPredictions where
  userid = @UserId
  and ModuleId = @ModuleId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[NCAAGetResults] 
  @ModuleId as int
AS

/*
  This module gets the initial pairings and unions the game results
*/

select * from (
select
  a.GameId,
  b.TeamId,
  b.TeamName,
  'GameScore' = 0,
  'TeamAScore' = 0,
  'TeamBScore' = 0
from 
  dbo.NCAAPairings a,
  dbo.NCAATeams b
where
  a.TeamId = b.TeamId
union
select
  a.GameId,
  b.TeamId,
  b.TeamName,
  a.GameScore,
  a.TeamAScore,
  a.TeamBScore
from 
  dbo.NCAAResults a,
  dbo.NCAATeams b
where
  a.TeamId = b.TeamId
  and a.ModuleId = @ModuleId
) a
order by GameId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[NCAAGetUserPicks]
  @ModuleId as int,
  @UserId as int
AS

/*
  This procedure gets the picks for the selected user, the ModuleId is not used in this version
  It gets the initial 64 team pairings and then unions the results of the user picks
*/

select * from (
select
  a.GameId,
  b.TeamId,
  b.TeamName,
  'GameScore' = 0,
  'TeamAScore' = 0,
  'TeamBScore' = 0
from 
  dbo.NCAAPairings a,
  dbo.NCAATeams b
where
  a.TeamId = b.TeamId
union
select
  a.GameId,
  b.TeamId,
  b.TeamName,
  a.GameScore,
  a.TeamAScore,
  a.TeamBScore
from 
  NCAAPredictions as a left outer join
  NCAATeams as b on 
    a.TeamId = b.TeamId 
    and a.Userid = @UserId 
    and a.ModuleId = @ModuleId 
) a
order by GameId
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [dbo].[NCAAInsertResults]
  @PortalId as int,
  @ModuleId as int,
  @GameId as varchar(5),
  @TeamName as varchar(100),
  @GameScore as int,
  @TeamAScore as int,
  @TeamBScore as int
 AS

/* 
  This procedure inserts game results, the module ID is not used in this version
  It first has to look up the team ID for the TeamName specified
*/

declare @Exists as varchar(5)
set @Exists = (select gameid from dbo.NCAAResults ia where ia.gameid = @GameId and ia.ModuleId = @ModuleId)

declare @TeamId as int
set @TeamId = (select teamid from dbo.NCAATeams ia where ia.teamname = @teamname)

IF @Exists is null BEGIN
insert into dbo.NCAAResults (
  PortalId,
  ModuleId,
  GameId,
  TeamId,  
  GameScore,
  TeamAScore,
  TeamBScore)
values (
  @PortalId,
  @ModuleId,
  @GameId,
  @TeamId,
  @GameScore,
  @TeamAScore,
  @TeamBScore)
END
ELSE
BEGIN
update dbo.NCAAResults set
  TeamId = @TeamId,
  GameScore = @GameScore,
  TeamAScore = @TeamAScore,
  TeamBScore = @TeamBScore
where
  GameId = @GameId
  and ModuleId = @ModuleId
END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[NCAAInsertUserPrediction]
  @PortalId as int,
  @ModuleId as int,
  @UserId as int,
  @GameId as varchar(5),
  @TeamName as varchar(100),
  @GameScore as int,
  @TeamAScore as int,
  @TeamBScore as int
AS

/* 
  This procedure inserts user predictions
  It first has to look up the team ID for the TeamName specified
*/

declare @TeamId as int
set @TeamId = (select teamid from dbo.NCAATeams ia where ia.teamname = @teamname)

insert into dbo.NCAAPredictions (
  PortalId,
  ModuleId,
  UserId,
  GameId,
  TeamId,
  GameScore,
  TeamAScore,
  TeamBScore)
values (
  @PortalId,
  @ModuleId,
  @UserId,
  @GameId,
  @TeamId,
  @GameScore,
  @TeamAScore,
  @TeamBScore)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[NCAAGetRoundPoints]
  @PortalId as int,
  @ModuleId as int
AS

/*
  This procedure gets number of points awarded for each round of the tournament
*/

select
  a.ModuleId,
  a.RoundId,
  a.RoundPoints
from 
  dbo.NCAARounds a
where a.PortalId = @PortalId
  and a.ModuleID = @ModuleId

GO

CREATE PROCEDURE [dbo].[NCAADeleteRoundPoints] 
  @PortalId as int,
  @ModuleId as int
AS

/*
  This procedure deletes all round points for the given module
*/

delete from dbo.NCAARounds where
  portalid = @PortalId and
  moduleid = @ModuleId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NCAAInsertRoundPoints]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NCAAInsertRoundPoints]
GO

CREATE PROCEDURE [dbo].[NCAAInsertRoundPoints]
  @PortalId as int,
  @ModuleId as int,
  @RoundId as int,
  @RoundPoints as int,
  @RoundGames as int
AS

/* 
  This procedure inserts round points
*/

insert into dbo.NCAARounds (
  PortalId,
  ModuleId,
  RoundId,
  RoundPoints, 
  RoundGames)
values (
  @PortalId,
  @ModuleId,
  @RoundId,
  @RoundPoints,
  @RoundGames)

GO

CREATE PROCEDURE [dbo].[NCAAListPoolStandings] 
  @PortalId as int,
  @ModuleId as int
AS

/*
  This procedure selects Pool Standings
*/

select
  UserId,
  UserName,
  UserFirstLastName,
  TotalPicks,
  CorrectPicks,
  IncorrectPicks,
  TotalPoints,
  Rank
from 
 fn_NCAAGetPoolStandingsByRank(@PortalId, @ModuleId) 
order by 
  rank

GO

CREATE PROCEDURE [dbo].[NCAAGetModuleList]
(
  @portalid as int
)
AS
select 
  a.tabid, 
  b.moduleid, 
  tabmodule = (a.tabname + '\' + b.moduletitle)
from  
  tabs a,
  modules b,
  tabmodules c
where
  a.tabid=c.tabid
  and b.moduleid = c.moduleid
  and a.isdeleted = 0 
  and a.portalid = @portalid
order by 
  a.tabname, b.moduletitle
GO

/* 
  NOW LETS MAKE SOME DATA INSERTS FOR THE TEAMS AND INITIAL PAIRINGS
*/

--clear the teams table and insert
delete from dbo.NCAATeams

--now insert the teams

SET NOCOUNT ON
 
SET IDENTITY_INSERT [dbo].[ncaateams] ON
GO
 
PRINT 'Inserting values into [ncaateams]'

INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(1,'Air Force')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(2,'Akron')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(3,'Alabama')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(4,'Alabama Birmingham')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(5,'Alabama State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(6,'Alcorn State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(7,'American')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(8,'Appalachian State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(9,'Arizona')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(10,'Arizona State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(11,'Arkansas')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(12,'Arkansas Lr')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(13,'Arkansas State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(14,'Army')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(15,'Auburn')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(16,'Austin Peay')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(17,'Ball State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(18,'Baylor')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(19,'Bethune Cookman')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(20,'Boise State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(21,'Boston College')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(22,'Bowling Green')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(23,'Bradley')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(24,'Brown')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(25,'Bucknell')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(26,'Buffalo')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(27,'Butler')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(28,'BYU')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(29,'California')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(30,'Campbell')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(31,'Canisius')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(32,'Centenary')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(33,'Central Conn')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(34,'Central Conn St.')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(35,'Central Florida')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(36,'Central Michigan')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(37,'Charleston Sou')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(38,'Charlotte')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(39,'Chicago State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(40,'Cincinnati')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(41,'Clemson')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(42,'Cleveland State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(43,'Coast Carolina')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(44,'Colgate')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(45,'Coll Of Charltn')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(46,'Colorado')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(47,'Colorado State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(48,'Columbia')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(49,'Connecticut')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(50,'Coppin State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(51,'Cornell')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(52,'Creighton')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(53,'Cs Northridge')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(54,'Cs Sacramento')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(55,'Dartmouth')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(56,'Davidson')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(57,'Dayton')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(58,'Delaware')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(59,'Delaware State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(60,'Depaul')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(61,'Detroit')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(62,'Drake')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(63,'Drexel')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(64,'Duke')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(65,'Duquesne')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(66,'East Carolina')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(67,'East Tenn State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(68,'Eastern Illinois')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(69,'Eastern Ky')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(70,'Eastern Michigan')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(71,'Eastern Washington')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(72,'Evansville')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(73,'Fair Dickinson')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(74,'Fairfield')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(75,'Fla Atlantic')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(76,'Florida')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(77,'Florida A&&M')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(78,'Florida Intl')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(79,'Florida State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(80,'Fordham')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(81,'Fresno State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(82,'Fullerton State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(83,'Furman')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(84,'George Mason')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(85,'George Washington')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(86,'Georgetown')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(87,'Georgia')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(88,'Georgia Southern')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(89,'Georgia State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(90,'Georgia Tech')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(91,'Gonzaga')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(92,'Grambling')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(93,'Hampton')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(94,'Hartford')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(95,'Harvard')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(96,'Hawaii')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(97,'Hofstra')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(98,'Holy Cross')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(99,'Houston')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(100,'Howard')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(101,'Idaho')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(102,'Idaho State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(103,'Illinois')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(104,'Illinois Chi')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(105,'Illinois State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(106,'Indiana')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(107,'Indiana State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(108,'Iona')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(109,'Iowa')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(110,'Iowa State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(111,'IUPUI')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(112,'Jackson State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(113,'Jacksonville')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(114,'Jacksonville State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(115,'James Madison')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(116,'Kansas')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(117,'Kansas State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(118,'Kent')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(119,'Kentucky')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(120,'Lafayette')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(121,'La-Lafayette')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(122,'Lamar')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(123,'Lasalle')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(124,'Lehigh')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(125,'Liberty')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(126,'Liu Brooklyn')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(127,'Long Beach State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(128,'Louisiana Tech')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(129,'Louisville')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(130,'Loyola Chi')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(131,'Loyola Marymnt')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(132,'Loyola Md')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(133,'LSU')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(134,'Maine')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(135,'Manhattan')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(136,'Marist')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(137,'Marquette')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(138,'Marshall')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(139,'Maryland')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(140,'Massachusetts')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(141,'Mcneese State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(142,'Md Baltimore Co')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(143,'Md Eastrn Shore')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(144,'Memphis')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(145,'Mercer')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(146,'Miami Fla')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(147,'Miami Ohio')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(148,'Michigan')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(149,'Michigan State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(150,'Middle Tenn State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(151,'Minnesota')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(152,'Miss Valley State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(153,'Mississippi')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(154,'Mississippi State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(155,'Missouri')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(156,'Mo Kansas City')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(157,'Monmouth Nj')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(158,'Montana')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(159,'Montana State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(160,'Morehead State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(161,'Morgan State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(162,'Mt St Marys')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(163,'Murray State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(164,'N. Carolina A&&T')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(165,'N. Carolina State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(166,'Navy')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(167,'NC Asheville')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(168,'NC Charlotte')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(169,'NC Greensboro')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(170,'NC Wilmington')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(171,'NE Illinois')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(172,'NE Louisiana')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(173,'Nebraska')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(174,'Nev Las Vegas')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(175,'Nevada')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(176,'New Hampshire')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(177,'New Mexico')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(178,'New Mexico State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(179,'New Orleans')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(180,'Niagara')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(181,'Nicholls State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(182,'North Carolina')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(183,'North Texas')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(184,'Northeastern')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(185,'Northern Arizona')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(186,'Northern Illinois')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(187,'Northern Iowa')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(188,'Northwestern')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(189,'Northwestern State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(190,'Notre Dame')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(191,'Ohio')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(192,'Ohio State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(193,'Oklahoma')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(194,'Oklahoma State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(195,'Old Dominion')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(196,'Ole Miss')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(197,'Oral Roberts')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(198,'Oregon')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(199,'Oregon State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(200,'Pacific')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(201,'Penn State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(202,'Pennsylvania')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(203,'Pepperdine')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(204,'Pittsburgh')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(205,'PLAY-IN')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(206,'Portland')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(207,'Prairie View')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(208,'Princeton')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(209,'Providence')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(210,'Purdue')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(211,'Radford')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(212,'Rhode Island')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(213,'Rice')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(214,'Richmond')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(215,'Rider')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(216,'Robert Morris')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(217,'Rutgers')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(218,'S. Carolina State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(219,'S. Mississippi')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(220,'Sam Houston')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(221,'Samford')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(222,'San Diego')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(223,'San Diego State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(224,'San Francisco')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(225,'San Jose State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(226,'Santa Barbara')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(227,'Santa Clara')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(228,'SE Louisiana')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(229,'SE Missouri State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(230,'Seton Hall')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(231,'Siena')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(232,'SMU')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(233,'South Alabama')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(234,'South Carolina')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(235,'South Florida')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(236,'Southern')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(237,'Southern Illinois')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(238,'Southern Utah')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(239,'St. Bonaventure')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(240,'St. Francis Ny')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(241,'St. Francis Pa')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(242,'St. Johns')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(243,'St. Josephs Pa')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(244,'St. Louis')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(245,'St. Marys Ca')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(246,'St. Peters')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(247,'Stanford')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(248,'Stephen Austin')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(249,'Stetson')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(250,'SW Louisiana')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(251,'SW Missouri State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(252,'SW Texas State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(253,'Syracuse')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(254,'TCU')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(255,'Temple')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(256,'Tenn Martin')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(257,'Tennessee')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(258,'Tennessee Chat')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(259,'Tennessee State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(260,'Tennessee Tech')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(261,'Texas')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(262,'Texas A&&M')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(263,'Texas Arlington')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(264,'Texas El Paso')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(265,'Texas San Antonio')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(266,'Texas Southern')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(267,'Texas Tech')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(268,'The Citadel')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(269,'Toledo')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(270,'Towson State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(271,'Troy State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(272,'Tulane')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(273,'Tulsa')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(274,'TX Pan American')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(275,'UC Irvine')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(276,'UCLA')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(277,'USC')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(278,'Utah')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(279,'Utah State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(280,'Va Commonwealth')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(281,'Valparaiso')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(282,'Vanderbilt')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(283,'Vermont')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(284,'Villanova')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(285,'Virginia')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(286,'Virginia Tech')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(287,'VMI')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(288,'Wagner')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(289,'Wake Forest')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(290,'Washington')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(291,'Washington State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(292,'Weber State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(293,'Western Carolina')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(294,'Western Illinois')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(295,'Western Ky')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(296,'Western Michigan')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(297,'Wichita State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(298,'William && Mary')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(299,'Winthrop')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(300,'Wisc Green Bay')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(301,'Wisc Milwaukee')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(302,'Wisconsin')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(303,'Wofford')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(304,'Wright State')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(305,'Wyoming')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(306,'Xavier Ohio')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(307,'Chattanooga')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(308,'West Virginia')
INSERT INTO [ncaateams] ([TeamId],[TeamName])VALUES(309,'Oakland')

PRINT 'Done'
 
 
SET IDENTITY_INSERT [dbo].[ncaateams] OFF
GO
SET NOCOUNT OFF


--delete the team pairings
delete from dbo.ncaapairings

--now insert the team pairings
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id001',103,1)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id002',73,16)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id003',261,8)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id004',175,9)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id005',3,5)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id006',301,12)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id007',21,4)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id008',201,13)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id009',133,6)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id010',4,11)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id011',9,3)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id012',279,14)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id013',237,7)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id014',245,10)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id015',194,2)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id016',228,15)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id017',290,1)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id018',158,16)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id019',200,8)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id020',204,9)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id021',90,5)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id022',85,12)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id023',129,4)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id024',121,13)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id025',267,6)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id026',276,11)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id027',91,3)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id028',299,14)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id029',308,7)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id030',52,10)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id031',289,2)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id032',307,15)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id033',64,1)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id034',59,16)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id035',247,8)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id036',154,9)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id037',149,5)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id038',195,12)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id039',253,4)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id040',283,13)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id041',278,6)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id042',264,11)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id043',193,3)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id044',180,14)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id045',40,7)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id046',109,10)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id047',119,2)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id048',69,15)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id049',182,1)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id050',309,16)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id051',151,8)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id052',110,9)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id053',284,5)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id054',177,12)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id055',76,4)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id056',191,13)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id057',302,6)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id058',187,11)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id059',116,3)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id060',25,14)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id061',38,7)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id062',165,10)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id063',49,2)
INSERT INTO [NCAAPairings] ([GameId],[TeamId],[TeamSeed])VALUES('id064',35,15)


--delete Data from NCAAGameRound
delete from dbo.NCAAGameRound

-- Insert Data into NCAAGameRound
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id065',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id066',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id067',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id068',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id069',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id070',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id071',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id072',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id073',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id074',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id075',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id076',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id077',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id078',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id079',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id080',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id081',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id082',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id083',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id084',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id085',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id086',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id087',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id088',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id089',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id090',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id091',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id092',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id093',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id094',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id095',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id096',1)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id097',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id098',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id099',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id100',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id101',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id102',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id103',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id104',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id105',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id106',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id107',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id108',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id109',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id110',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id111',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id112',2)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id113',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id114',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id115',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id116',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id117',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id118',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id119',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id120',3)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id121',4)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id122',4)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id123',4)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id124',4)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id125',5)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id126',5)
INSERT INTO [NCAAGameRound] ([GameId],[RoundId])VALUES('id127',6)

--delete Data from NCAAPreceedingGames
delete from dbo.NCAAPreceedingGames

-- Insert Data into NCAAPreceedingGames
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id065','id001','id002')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id066','id003','id004')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id067','id005','id006')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id068','id007','id008')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id069','id009','id010')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id070','id011','id012')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id071','id013','id014')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id072','id015','id016')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id073','id017','id018')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id074','id019','id020')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id075','id021','id022')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id076','id023','id024')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id077','id025','id026')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id078','id027','id028')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id079','id029','id030')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id080','id031','id032')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id081','id033','id034')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id082','id035','id036')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id083','id037','id038')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id084','id039','id040')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id085','id041','id042')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id086','id043','id044')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id087','id045','id046')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id088','id047','id048')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id089','id049','id050')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id090','id051','id052')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id091','id053','id054')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id092','id055','id056')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id093','id057','id058')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id094','id059','id060')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id095','id061','id062')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id096','id063','id064')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id097','id065','id066')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id098','id067','id068')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id099','id069','id070')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id100','id071','id072')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id101','id073','id074')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id102','id075','id076')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id103','id077','id078')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id104','id079','id080')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id105','id081','id082')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id106','id083','id084')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id107','id085','id086')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id108','id087','id088')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id109','id089','id090')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id110','id091','id092')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id111','id093','id094')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id112','id095','id096')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id113','id097','id098')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id114','id099','id100')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id115','id101','id102')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id116','id103','id104')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id117','id105','id106')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id118','id107','id108')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id119','id109','id110')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id120','id111','id112')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id121','id113','id114')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id122','id115','id116')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id123','id117','id118')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id124','id119','id120')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id125','id121','id122')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id126','id123','id124')
INSERT INTO [NCAAPreceedingGames] ([GameId],[TeamAId],[TeamBId])VALUES('id127','id125','id126')

--*****************
-- Version 01.01.00
--*****************

if exists (select * from dbo.sysobjects where id = object_id(N'[NCAAClearResults]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [NCAAClearResults]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [NCAAClearResults] 
  @PortalId as int,
  @ModuleId as int
AS

/*
  This procedure deletes all results for the given module
*/

delete from NCAAResults where
  portalid = @PortalId and
  moduleid = @ModuleId

GO

if exists(select * from dbo.sysobjects where id = object_id(N'[vw_NCAAPoolStandings]') and OBJECTPROPERTY(id, N'IsView') = 1)
   drop view [vw_NCAAPoolStandings]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create view [vw_NCAAPoolStandings]
as
select
  u.UserId as 'UserId',
  b.PortalId as 'PredictionsPortalId',
  b.ModuleId as 'PredictionsModuleId',
  d.PortalId as 'RoundsPortalId',
  d.ModuleId as 'RoundsModuleId',
  max(u.username) as 'UserName',
  max(u.FirstName + ' '+ u.LastName) as 'UserFirstLastName',
  count(b.gameid) as 'TotalPicks',
sum((CASE 
    WHEN a.TeamId = B.TeamId THEN 1 
    ELSE 0 
    END) * d.RoundPoints) AS 'TotalPoints',
sum(CASE 
    WHEN a.TeamId = B.TeamId THEN 1 
    ELSE 0 
    END) AS 'CorrectPicks',
sum(
  CASE 
    WHEN a.TeamId = B.TeamId THEN 0 
    WHEN a.TeamId is NULL THEN 0
    ELSE 1
    END) AS 'IncorrectPicks'
from 
  ((Users as u
   INNER JOIN NCAAPredictions as b 
     ON b.UserId = u.Userid)
  INNER JOIN NCAAGameRound as c
     ON b.GameId = c.GameId
       INNER JOIN NCAARounds as d
          ON c.RoundId = d.RoundId)
   LEFT OUTER JOIN NCAAResults a
     ON b.GameId = a.GameId
        and b.ModuleId = a.ModuleId
group by 
  u.UserId,  
  b.PortalId,
  b.ModuleId,
  d.PortalId,
  d.ModuleId 

--*****************
-- Version 01.02.00
--*****************

if exists (select * from dbo.sysobjects where id = object_id(N'[fn_NCAACalculatePossiblePoints]') and xtype in (N'FN', N'IF', N'TF'))
drop function [fn_NCAACalculatePossiblePoints]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [fn_NCAACalculatePossiblePoints] 
(
  @gameid varchar(5),
  @winner int,
  @moduleid int
)
RETURNS int
AS
BEGIN

declare @result as int 

--set the possible points for this game

declare @roundpoints as int

set @roundpoints = (
select 
  roundpoints
from 
  ncaarounds a,
  ncaagameround b
where 
  a.roundid = b.roundid
  and b.gameid = @gameid)

declare @notwinner as int

set @notwinner = (select case when isnull(@winner,1) = 1 then 1 else 0 end)

if @notwinner = 0 begin
set @notwinner =
(select 
  count(*)
from (
select 
  a.gameid,
  a.teamaid,
  a.teambid,
  'winner' = b.teamid,
  'teama' = c.teamid,
  'teamb' = d.teamid
from 
  ncaapreceedinggames a,
  (select
    ia.gameid,
    ia.teamid
  from 
    ncaaresults ia
  where 
    ia.moduleid = @moduleid
  union
  select
    ib.gameid,
    ib.teamid
   from
     ncaapairings ib) b,
    (select
    ia.gameid,
    ia.teamid
  from 
    ncaaresults ia
  where 
    ia.moduleid = @moduleid
  union
  select
    ib.gameid,
    ib.teamid
   from
     ncaapairings ib)  c,
   (select
    ia.gameid,
    ia.teamid
  from 
    ncaaresults ia
  where 
    ia.moduleid = @moduleid
  union
  select
    ib.gameid,
    ib.teamid
   from
     ncaapairings ib)  d
where 
  a.gameid between 'id065' and @gameid
  and a.gameid = b.gameid
  and a.teamaid = c.gameid
  and a.teambid = d.gameid
) a  
where
  a.winner <> @winner
  and (a.teama = @winner
    or a.teamb = @winner)
 )
end

set @result = (select case when @notwinner > 0 then 0 else @roundpoints end)
  return @result
END
GO

ALTER view [vw_NCAAPoolStandings]
as
select
  u.UserId as 'UserId',
  b.PortalId as 'PredictionsPortalId',
  b.ModuleId as 'PredictionsModuleId',
  d.PortalId as 'RoundsPortalId',
  d.ModuleId as 'RoundsModuleId',
  max(u.username) as 'UserName',
  max(u.FirstName + ' '+ u.LastName) as 'UserFirstLastName',
  count(b.gameid) as 'TotalPicks',
sum((CASE 
    WHEN a.TeamId = B.TeamId THEN 1 
    ELSE 0 
    END) * d.RoundPoints) AS 'TotalPoints',
sum(CASE 
    WHEN a.TeamId = B.TeamId THEN 1 
    ELSE 0 
    END) AS 'CorrectPicks',
sum(
  CASE 
    WHEN a.TeamId = B.TeamId THEN 0 
    WHEN a.TeamId is NULL THEN 0
    ELSE 1
    END) AS 'IncorrectPicks',
sum(fn_NCAACalculatePossiblePoints(b.gameid, b.teamid, b.moduleid)) as 'PossiblePoints'
from 
  ((Users as u
   INNER JOIN NCAAPredictions as b 
     ON b.UserId = u.Userid)
  INNER JOIN NCAAGameRound as c
     ON b.GameId = c.GameId
       INNER JOIN NCAARounds as d
          ON c.RoundId = d.RoundId)
   LEFT OUTER JOIN NCAAResults a
     ON b.GameId = a.GameId
        and b.ModuleId = a.ModuleId
group by 
  u.UserId,  
  b.PortalId,
  b.ModuleId,
  d.PortalId,
  d.ModuleId
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fn_NCAAGetPoolStandingsByRank]') and xtype in (N'FN', N'IF', N'TF'))
drop function [fn_NCAAGetPoolStandingsByRank]
GO

create function  [fn_NCAAGetPoolStandingsByRank](@PortalId int, @ModuleId int)
returns table
as

return 
(
select 
  UserId,
  PredictionsPortalId,
  PredictionsModuleId,
  RoundsPortalId,
  RoundsModuleId,
  UserName,
  UserFirstLastName,
  TotalPicks,
  TotalPoints,
  CorrectPicks,
  IncorrectPicks, 
  PossiblePoints,
  (select count (s2.TotalPoints)
from vw_NCAAPoolStandings s2
where
s1.TotalPoints < s2.TotalPoints) + 1 as  'rank'
from vw_NCAAPoolStandings s1
where PredictionsPortalId = @PortalId
  and PredictionsModuleId = @ModuleId
  and RoundsPortalId = @PortalId
  and RoundsModuleId = @ModuleId

)

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

 

